﻿/*
'Description:分厂工序排产完成率看板(生产作业会)中间表结转
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:zhunan
'create Date:2025.2.24
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using UFC.Common;
using UFC.DBUtility;
using System.Collections;
using System.Data;
using System.Configuration;

namespace TransferMesReportForm
{
    //分厂工序排产完成率看板(生产作业会)
    class TransferContainerStepPlanBoard
    {
        private static string mesReportConn = ConfigurationManager.AppSettings["MesReportOracleConn"];
        //入口
        public void RunMain()
        {
            LogHelp.WriteLog("TransferContainerStepPlanBoard服务循环开始---------------------");

            System.Threading.Thread thAddData = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    AddData();//添加数据
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferContainerStepPlanBoard服务-添加数据", e);
                }
            }));
            thAddData.Start();

            System.Threading.Thread thUpdateCheckDateAndStatus = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    updateCheckDateAndStatus();//更新检验时间及状态数据
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferContainerStepPlanBoard服务-更新检验时间及状态数据", e);
                }
            }));
            thUpdateCheckDateAndStatus.Start();

            System.Threading.Thread thUpdateIsuse = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    updateIsuse();//更新isuse字段数据
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferContainerStepPlanBoard服务-更新isuse字段数据", e);
                }
            }));
            thUpdateIsuse.Start();

            System.Threading.Thread thDeleteData = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    deleteData();//删除数据
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferContainerStepPlanBoard服务-删除数据", e);
                }
            }));
            thDeleteData.Start();

            //线程等待
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thAddData.ThreadState != System.Threading.ThreadState.Stopped);
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thUpdateCheckDateAndStatus.ThreadState != System.Threading.ThreadState.Stopped);
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thUpdateIsuse.ThreadState != System.Threading.ThreadState.Stopped);
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thDeleteData.ThreadState != System.Threading.ThreadState.Stopped);

            LogHelp.WriteLog("TransferContainerStepPlanBoard服务循循环结束---------------------");
        }

        //增量数据
        public void AddData()
        {
            string strSql = @"
SELECT f.factoryid,f.factoryname,f.factorycode,csp.containerid,cci.containername,csp.workflowstepname,
       SUBSTR(csp.workflowstepname,1,INSTR(csp.workflowstepname,'-')-1) specno,
       SUBSTR(csp.workflowstepname,INSTR(csp.workflowstepname,'-')+1) specdesc,
       csp.plannedfinishdate,MAX(csd.checkdate) checkdate,
       CASE WHEN MAX(csd.checkdate) IS NULL THEN 0
           WHEN MAX(csd.checkdate) IS NOT NULL AND TRUNC(MAX(csd.checkdate)) > TRUNC(csp.plannedfinishdate) THEN 2
           WHEN MAX(csd.checkdate) IS NOT NULL AND TRUNC(MAX(csd.checkdate)) <= TRUNC(csp.plannedfinishdate) THEN 1
       END status,
       cci.mfgordername,cci.fromworkcentername,MAX(csp.stepplancreatedate) stepplancreatedate,csp.workflowstepid
FROM containerstepplan@insitedb74 csp
LEFT JOIN r_containerstepplanboard r ON r.containerid = csp.containerid AND r.workflowstepid = csp.workflowstepid
LEFT JOIN containercurrentinfo@insitedb74 cci ON cci.containerid = csp.containerid
LEFT JOIN mes_factory_mapping@insitedb74 f ON f.factoryid = cci.originalfactoryid
LEFT JOIN containersteprecord@insitedb74 csd ON csd.containerid = csp.containerid AND csd.workflowstepid = csp.workflowstepid
WHERE csp.plantype = 1 AND csp.plannedfinishdate >= to_date('2024/10/01','yyyy/mm/dd')
    AND r.containerid IS NULL AND cci.containerstatus <> 2
GROUP BY f.factoryid,f.factoryname,f.factorycode,csp.containerid,cci.containername,csp.workflowstepname,csp.plannedfinishdate,
        cci.mfgordername,cci.fromworkcentername,csp.stepplancreatedate,csp.workflowstepid";
            var dt = OracleHelper.Query(strSql, mesReportConn).Tables[0];
            if (dt.Rows.Count > 0)
            {
                var insertSqlList = new ArrayList();
                foreach (DataRow dr in dt.Rows)
                {
                    var insertSql = @"
INSERT INTO r_containerstepplanboard r(
    factoryid,factoryname,factorycode,containerid,containername,
    workflowstepname,specno,specdesc,plannedfinishdate,checkdate,
    status,mfgordername,fromworkcentername,stepplancreatedate,workflowstepid,id)
VALUES('{0}','{1}','{2}','{3}','{4}',
       '{5}','{6}','{7}',to_date('{8}','yyyy/MM/dd hh24:mi:ss'),to_date('{9}','yyyy/MM/dd hh24:mi:ss'),
       '{10}','{11}','{12}',to_date('{13}','yyyy/MM/dd hh24:mi:ss'),'{14}','{15}')";

                    insertSqlList.Add(string.Format(insertSql,
                        dr["factoryid"].ToString(), dr["factoryname"].ToString(), dr["factorycode"].ToString(), dr["containerid"].ToString(), dr["containername"].ToString(),
                        dr["workflowstepname"].ToString(), dr["specno"].ToString(), dr["specdesc"].ToString(), dr["plannedfinishdate"].ToString(), dr["checkdate"].ToString(),
                        dr["status"].ToString(), dr["mfgordername"].ToString(), dr["fromworkcentername"].ToString(), dr["stepplancreatedate"].ToString(), dr["workflowstepid"].ToString(),
                        Guid.NewGuid().ToString()));
                }
                OracleHelper.ExecuteSqlTran(insertSqlList, mesReportConn);
                LogHelp.WriteLog("TransferContainerStepPlanBoard服务-添加中间表r_containerstepplanboard数据条数：" + insertSqlList.Count);
            }
        }

        //更新检验时间及状态
        public void updateCheckDateAndStatus()
        {
            string strSql = @"
SELECT t.containerid,t.containername,t.workflowstepname,t.plannedfinishdate,
       MAX(csd.checkdate) checkdate,
       CASE WHEN MAX(csd.checkdate) IS NULL THEN 0
           WHEN MAX(csd.checkdate) IS NOT NULL AND TRUNC(MAX(csd.checkdate)) > TRUNC(t.plannedfinishdate) THEN 2
           WHEN MAX(csd.checkdate) IS NOT NULL AND TRUNC(MAX(csd.checkdate)) <= TRUNC(t.plannedfinishdate) THEN 1
       END status
FROM r_containerstepplanboard t
LEFT JOIN containersteprecord@insitedb74 csd ON csd.containerid = t.containerid AND csd.workflowstepname = t.workflowstepname
WHERE t.status = 0 AND csd.checkdate IS NOT NULL
GROUP BY t.containerid,t.containername,t.workflowstepname,t.plannedfinishdate";
            var dt = OracleHelper.Query(strSql, mesReportConn).Tables[0];
            if (dt.Rows.Count > 0)
            {
                var updateSqlList = new ArrayList();
                foreach (DataRow dr in dt.Rows)
                {
                    var updateSql = @"
UPDATE r_containerstepplanboard t SET t.checkdate = to_date('{0}','yyyy/mm/dd hh24:mi:ss'),t.status = '{1}' 
WHERE t.containerid = '{2}' AND t.workflowstepname = '{3}'";
                    updateSqlList.Add(string.Format(updateSql, dr["checkdate"].ToString(), dr["status"].ToString(), dr["containerid"].ToString(), dr["workflowstepname"].ToString()));
                }
                OracleHelper.ExecuteSqlTran(updateSqlList, mesReportConn);
                LogHelp.WriteLog("TransferContainerStepPlanBoard服务-更新中间表r_containerstepplanboard【检验时间及状态】数据条数：" + updateSqlList.Count);
            }
        }

        //更新isuse字段
        public void updateIsuse()
        {
            string strSql = @"
SELECT t.containerid,t.workflowstepid
FROM r_containerstepplanboard t
INNER JOIN (
SELECT t.containerid,t.workflowstepid
FROM
(
    SELECT s.containerid,s.workflowstepid
    FROM containerstepnoship@insitedb74 s
    WHERE s.isuse = 0 AND s.noship = 2
    UNION ALL
    SELECT cm.containerid,cm.workflowstepid
    FROM containercommidetail@insitedb74 cm
    WHERE cm.isuse = 0
) t
GROUP BY t.containerid,t.workflowstepid
) m ON m.containerid = t.containerid AND m.workflowstepid = t.workflowstepid
WHERE NVL(t.isuse,0) = 0";
            var dt = OracleHelper.Query(strSql, mesReportConn).Tables[0];
            if (dt.Rows.Count > 0)
            {
                var updateSqlList = new ArrayList();
                foreach (DataRow dr in dt.Rows)
                {
                    var updateSql = "UPDATE r_containerstepplanboard a SET a.isuse = '1' WHERE a.containerid = '{0}' AND a.workflowstepid = '{1}'";
                    updateSqlList.Add(string.Format(updateSql, dr["containerid"].ToString(), dr["workflowstepid"].ToString()));
                }
                OracleHelper.ExecuteSqlTran(updateSqlList, mesReportConn);
                LogHelp.WriteLog("TransferContainerStepPlanBoard服务-更新中间表r_containerstepplanboard【isuse字段】数据条数：" + updateSqlList.Count);
            }
        }

        //删除数据
        public void deleteData()
        {
            string strSql = @"
SELECT t.containerid,t.workflowstepid
FROM r_containerstepplanboard@mesreport t
LEFT JOIN containerstepplan csp ON csp.containerid = t.containerid AND csp.workflowstepid = t.workflowstepid AND csp.plantype = 1
WHERE csp.containerid IS NULL AND ROWNUM <= 4000

UNION ALL

SELECT a.containerid,a.workflowstepid
FROM r_containerstepplanboard@mesreport a
INNER JOIN containercurrentinfo cci ON a.containerid = cci.containerid
WHERE cci.containerstatus = 2";
            var dt = OracleHelper.Query(strSql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                var deleteSqlList = new ArrayList();
                foreach (DataRow dr in dt.Rows)
                {
                    var deleteSql = "DELETE r_containerstepplanboard tt WHERE tt.containerid = '{0}' AND tt.workflowstepid = '{1}'";
                    deleteSqlList.Add(string.Format(deleteSql, dr["containerid"].ToString(), dr["workflowstepid"].ToString()));
                }
                OracleHelper.ExecuteSqlTran(deleteSqlList, mesReportConn);
                LogHelp.WriteLog("TransferContainerStepPlanBoard服务-删除中间表r_containerstepplanboard数据条数：" + deleteSqlList.Count);
            }
        }
    }
}